using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using AutoMapper;
using AutoMapper.Configuration.Annotations;
using Microsoft.EntityFrameworkCore;
using ShopForWeb.Model.Basic;
using ShopForWeb.Model.DBContexts;
using ShopForWeb.Model.View;

namespace ShopForWeb.Services
{
    public class OrderService
    {
        private ShopDBContext   _db;
        private IMapper _map;
        public  OrderService (ShopDBContext db,IMapper map)
        {
            _db=db;
            _map=map;
        }
        public async Task<int> AddNewAsync(Order order,List<OrderDetail> details)
        {
            order.OrderId=Guid.NewGuid().ToString("N");
            order.OrderDate=DateTime.Now.ToString("yyyy-MM-dd");
            order.StatusCode=1;
            order.TotalAmount=0;
            foreach(var obj in details)
            {
                order.TotalAmount+=obj.UnitPrice*obj.Quantity;
                obj.OrderId=order.OrderId;
            }
            await _db.OrderSet.AddAsync(order);
            await _db.OrderDetailSet.AddRangeAsync(details);
            return await _db.SaveChangesAsync();
        }
        public async Task<int> RemoveAsync(string orderId)
        {
           var trans= await _db.Database.BeginTransactionAsync();

            await _db.OrderDetailSet
            .Where(od=>od.OrderId==orderId)
            .ExecuteDeleteAsync();
           int result= await _db.OrderSet
           .Where(o=>o.OrderId==orderId&&o.StatusCode==1)
            .ExecuteDeleteAsync();
            if(result==1) await trans.CommitAsync();
            else await trans.RollbackAsync();
            return result;
        }
     
  public async Task<List<MonthReportView>> GetMonthReportAsync(int year)
  {
    var query=_db.Database.SqlQueryRaw<MonthReportView>(
        """
        select a.month,ISNULL(b.TotalAmount,0) as TotalAmount
        from (select number as [month]
        from master..spt_values
        where type = 'P' and number between 1 AND 12) a

        left join (select MONTH(OrderDate) as month, 
        Sum(TotalAmount) as TotalAmount
        from T_Order
        where YEAR(orderDate)={0} and statusCode in (2,3,4)
        group by MONTH(OrderDate)) b
        on a.month=b.month
        """,[year]
    );
    return await query.ToListAsync();

  }
  public async Task<List<OrderView>> GetPageAsync(int page,int size,string phone,int statusCode)
  {
    var query = _db.OrderSet
    .AsNoTracking()
    .Include(o =>o.OwnUser)
    .Where(o =>1 == 1);
    if(!string.IsNullOrEmpty(phone))
    query = query.Where(o =>o.OwnUser.Phone == phone);
    if(statusCode !=0)
    query = query.Where(o =>o.StatusCode == statusCode);
    query = query.OrderByDescending(o =>o.OrderId)
      .Skip((page -1)* size)
      .Take(size);
      return _map.Map<List<OrderView>>(await query.ToListAsync());
  }
  public Order GetById(string orderId)
  {
     throw new NotImplementedException();
  }
  public async Task<OrderView?> GetByIdAsync(string orderId)
  {
    var query=_db.OrderSet.Where(o=>o.OrderId==orderId);
    return _map.Map<OrderView> (await query.FirstOrDefaultAsync());
  }
  public int ModifyStatusCode(string orderId,int statusCode)
  {
    throw new NotImplementedException();
  }
  public async Task<int>GetTotalAsync(string phone,int statusCode)
  {
    var query = _db.OrderSet
    .AsNoTracking()
    .Include(o =>o.OwnUser)
    .Where(o =>1 == 1);
    if(!string.IsNullOrEmpty(phone))
    query = query.Where(o =>o.OwnUser.Phone == phone);
    if(statusCode !=0)
    query = query.Where(o =>o.StatusCode == statusCode);
    return await query.CountAsync();
  }

     
    }
}